package com.qcby.mybatis01.jdbc;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;




public class MysqlUtil {

	public static void main(String[] args) {
		 String sql ="select * from jc_api_info order by id desc limit 0,10";
		 String[] col = {"id","api_name","api_url","api_code","disabled","limit_call_day"};
		 String json = getJsonBySql(sql,col);
		
	}
	/**
	 * 鐢ㄤ簬鎻掑叆鏁版嵁
	 * @param sql insert璇彞
	 * @return
	 */
	public static int add(String sql) {
		
		System.out.println("sql鎻掑叆璇彞鏄�:");
        int i=0;
        //鏁版嵁搴撹繛鎺�
        DBConnection db = new DBConnection();
        try {        
            PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
            preStmt.executeUpdate();
            preStmt.close();
            db.close();//鍏抽棴杩炴帴 
            i = 1;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return i;//杩斿洖褰卞搷鐨勮鏁帮紝1涓烘墽琛屾垚鍔�;
    }
    //
    public static ArrayList<String[]> showUtil( String sql, String[] colums){
        
    	 ArrayList<String[]>  result = new  ArrayList<String[]>();
         DBConnection db = new DBConnection();
         try {
            Statement stmt = (Statement) db.conn.createStatement();
            ResultSet rs = (ResultSet) stmt.executeQuery(sql);
           
            while(rs.next()){
               String[] dataRow = new String[colums.length];
               for( int i = 0; i < dataRow.length; i++ ) {
            	   dataRow[i] = rs.getString( colums[i] );
               }
               result.add(dataRow);
            }
            rs.close();
            db.close();//
        } catch (SQLException e) {
            e.printStackTrace();
        } 
         
         return result;
    }
    
    public static String getJsonBySql( String sql, String[] colums){
    	
    System.out.println("瑕佹墽琛岀殑sql璇彞鏄細" + sql);
        
   	 ArrayList<String[]>  result = new  ArrayList<String[]>();
        DBConnection db = new DBConnection();
        try {
           Statement stmt = (Statement) db.conn.createStatement();
           ResultSet rs = (ResultSet) stmt.executeQuery(sql);
          
           while(rs.next()){
              String[] dataRow = new String[colums.length];
              for( int i = 0; i < dataRow.length; i++ ) {
           	   dataRow[i] = rs.getString( colums[i] );
              }
              result.add(dataRow);
           }
           rs.close();
           db.close();//
       } catch (SQLException e) {
           e.printStackTrace();
       } 
        
        return listToJson(result);
   }

    public static int update(String sql) {
        int i =0;
        DBConnection db = new DBConnection();
        try {
            PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
            preStmt.executeUpdate();
            preStmt.close();
            db.close();
            i = 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public static int updatePre(String sql) {
        int i =0;
        DBConnection db = new DBConnection();
        try {
            PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
            preStmt.setString(1,"w' or  '1' = '1'");
            preStmt.executeUpdate();
            preStmt.close();
            db.close();
            i = 1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    

    /**
     *  鏌ヨ鏁版嵁
     * @param sql select * from 琛�
     * @param params [id,name,sex,age] 鎴戜滑瑕佹煡璇㈢殑鍒楀悕鐨勪竴涓瓧绗︿覆鏁扮粍
     * @return
     */
    public static String show(String sql, String[] params){
    	
    	List< Map<String,String> > listmap = new ArrayList<>();
    	
         DBConnection db = new DBConnection();
         ResultSet rs = null;
         try {
            Statement stmt = (Statement) db.conn.createStatement();
            rs = (ResultSet) stmt.executeQuery(sql);
            while(rs.next()){
            	Map<String,String> map = new HashMap<String,String>();
            	for(int i = 0; i < params.length; i++) {
            		map.put(params[i], rs.getString(params[i]));
            	}
            	listmap.add(map);
            }
            rs.close();
            db.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
		return mapToJson(listmap);     
    }

    public static String showPre(String sql, String[] params){

        List< Map<String,String> > listmap = new ArrayList<>();

        DBConnection db = new DBConnection();
        ResultSet rs = null;
        try {
            PreparedStatement stmt =  db.conn.prepareStatement(sql);
            stmt.setString(1,"1' or '1'='1'");
//            stmt.setInt(2,2);
            rs = (ResultSet) stmt.executeQuery();
            while(rs.next()){
                Map<String,String> map = new HashMap<String,String>();
                for(int i = 0; i < params.length; i++) {
                    map.put(params[i], rs.getString(params[i]));
                }
                listmap.add(map);
            }
            rs.close();
            db.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return mapToJson(listmap);
    }




    /**
     * [
     * 	{"id":"xxx","title":"testxx"},
     * 	{"id":"xxx1","title1":"testxx2"}
     * ]
     * @param sql
     * @param params
     * @return
     */
    public static List< Map<String,String> > showListMap(String sql, String[] params){
    	
    	List< Map<String,String> > listmap = new ArrayList<>();

         DBConnection db = new DBConnection();
         ResultSet rs = null;
         try {
            Statement stmt = (Statement) db.conn.createStatement();
            rs = (ResultSet) stmt.executeQuery(sql);
            while(rs.next()){
            	Map<String,String> map = new HashMap<String,String>();
            	for(int i = 0; i < params.length; i++) {
            		map.put(params[i], rs.getString(params[i]));
            	}
            	listmap.add(map);
            }
            rs.close();
            db.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
		return listmap;     
    }
    




    
  
    public static int del(String delstr) {
    	System.out.println("del:" + delstr);
        int i=0;
        DBConnection db = new DBConnection();
        try {    
            PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(delstr);
            preStmt.executeUpdate();
            
            preStmt.close();
            db.close();
            i = 1;
        } catch (SQLException e){
            e.printStackTrace();
        }
        return i;
    }

    
    /**
     * map杞寲涓簀son鏁版嵁瀛楃涓�
     * @param maplist
     * @return
     */
    public static String mapToJson( List<Map<String,String>> maplist ) {
    	String jsonData = "{ \"data\":[";
		for(int i = 0; i < maplist.size(); i++) {
			String outstr = "[\"" ;
			int size = 0;
			for(String value : maplist.get(i).values()){
				size += 1;
				outstr += value;
				if( size < maplist.get(i).values().size() ) {
				     outstr += "\",\"";
				}
			}
		    outstr += "\"]";
		    
		    if(i < maplist.size() -1) {
		    	outstr += ",";
		    }
			jsonData += outstr;
			
		}
		jsonData += "]}";
		
		return jsonData;
    }
    
    public static String listToJson( ArrayList<String[]> list) {
    	
//    	String jsonStr = "{\"code\":200,\"message\":\"鎴愬姛浜哱",\"data\":[";
//    			for(int i = 0; i < list.size(); i++) {
//    				
//    				String arr = "[";
//    				for( int j = 0; j < list.get(0).length; j++) {
//    					if( list.get(i)[j] != null) {
//    						arr += "\"" + list.get(i)[j].replace("\"", "\\\"") + "\"";
//    					}
//    					
//    					if( j < list.get(0).length - 1 ) {
//    						arr += ",";
//    					}
//    				}
//    				arr += "]";
//    				if( i < list.size() - 1 ) {
//						arr += ",";
//					}
//    				
//    				jsonStr += arr;
//    			}
//    			jsonStr += "]}";
    	
    	return null;
    }
    
    
	/**
	 * 
	 * @param fieldsArr 瀛楁鏁扮粍锛屾寜椤哄簭鎷兼帴
	 * eg:  test1,test2,test3 => 'test1','test2','test3'
	 * @return
	 */
	public static String getFieldsSql(String[] fieldsArr) {
		String fieldsSql="";
		for (int i = 0; i < fieldsArr.length; i++) {
			fieldsSql += formatField(fieldsArr[i]);
			if(i != fieldsArr.length -1) {
				fieldsSql += ",";
			}
		}
		return fieldsSql;
	}
	
	public static String formatField(String field) {
		return "'"+field+"'";
	}
}
	
	

